﻿/*
 * Сделано в SharpDevelop.
 * Пользователь: Badanin M.V.
 * Дата: 14.05.2013
 * Время: 11:07
 * 
 */
#define ExportXMLS
using System;
using System.Text.RegularExpressions;
using System.Xml;
using System.Text;
using System.Collections.Generic;

namespace Region.Utils
{
  /// <summary>
  /// Description of ExportXLSX.
  /// </summary>
  public class ExportXLSX: ExportOffice
  {
    public ExportXLSX():base("xl"){}   
    Dictionary<Int32,String> StringList = new Dictionary<Int32, String>();
    Dictionary<String,Int32> StringUni = new Dictionary<String,Int32>();
    String GetString(Int32 N){return StringList[N];}
    String GetString(String N){Int32 n=-1; if (Int32.TryParse(N,out n)) return StringList[n]; return null;}
    Int32 AddString(String Name)
    {
      if (StringUni.ContainsKey(Name)) return StringUni[Name];
      Int32 N = StringList.Count;
      StringList[N] = Name;
      StringUni[Name] = N;
      return N;
    }
    bool LoadStrings()
    {
      XmlDocument sharedStrings = new XmlDocument();      
      sharedStrings.LoadXml(Encoding.UTF8.GetString(m_zip.readAll("xl/sharedStrings.xml")));      
      Int32 N = 0;
      foreach (XmlElement t in sharedStrings.GetElementsByTagName("t"))
      {
        StringList[N] = t.InnerText;
        StringUni[t.InnerText] = N;
        N+=1;
      }
      return true;
    }
    bool SaveStrings()
    {
      XmlDocument sharedStrings = new XmlDocument();          
      sharedStrings.InsertBefore(sharedStrings.CreateXmlDeclaration("1.0","UTF-8","yes"), sharedStrings.DocumentElement);      
      sharedStrings.AppendChild(sharedStrings.CreateElement("sst"));
      sharedStrings.DocumentElement.SetAttribute("uniqueCount",StringUni.Count.ToString());
      sharedStrings.DocumentElement.SetAttribute("count",StringUni.Count.ToString());// FIXME указаое не точное значение
      sharedStrings.DocumentElement.SetAttribute("xmlns","http://schemas.openxmlformats.org/spreadsheetml/2006/main");// FIXME указаое не точное значение      
     //<si><t>Поле1</t></si>-<si><t>Поле2</t></si>-<si><t>Дата</t></si></sst>
      foreach (var S1 in StringList)
      {
        var si = sharedStrings.CreateElement("si");
        var t = sharedStrings.CreateElement("t");
        t.InnerText = S1.Value;
        si.AppendChild(t);
        sharedStrings.DocumentElement.AppendChild(si);
      }      
      m_zip.writeAll("xl/sharedStrings.xml",Encoding.UTF8.GetBytes(sharedStrings.InnerXml));      
      return true;
    }
    bool setCellValue(XmlElement Cell,Object Value)
    {
      if (Value is String)
      {
        Cell.SetAttribute("t","s");
        Cell.FirstChild.InnerText = AddString((String)Value).ToString();
        return true;
      }
      if (Value is DateTime)
      {
        //Cell.SetAttribute("t","s");        
        if (Math.Abs(((DateTime)Value-new DateTime(1970,1,1)).TotalDays) >2)
        {
          Cell.RemoveAttributeNode(Cell.GetAttributeNode("t"));
          //Cell.SetAttribute("s","13");
          DateTime D = new DateTime(1900,1,1);
          Cell.FirstChild.InnerText = (((DateTime)Value).Subtract(new DateTime(1900,1,1).Date).Days+1+1).ToString();// 1 день добавляется потому что 01.01.1900 равно 1, вторая 1 добавляется изза того что в экселе неправильно считается высокосный год в 1900
        }
        else
        {
          Cell.SetAttribute("t","s");
          Cell.FirstChild.InnerText = AddString("").ToString();          
        }
        return true;
      }
      if (Value is RegionDate)
      {
        //Cell.SetAttribute("t","s");  
        
        if (!((RegionDate)Value).Empty)
        {
          Cell.RemoveAttributeNode(Cell.GetAttributeNode("t"));
          //Cell.SetAttribute("s","13");
          Cell.FirstChild.InnerText = (((RegionDate)Value).DateTime.Subtract(new DateTime(1900,1,1).Date).Days+1+1).ToString();// 1 день добавляется потому что 01.01.1900 равно 1, вторая 1 добавляется изза того что в экселе неправильно считается высокосный год в 1900
        }
        else
        {
          Cell.SetAttribute("t","s");
          Cell.FirstChild.InnerText = AddString("").ToString();          
        }
        return true;
      }
      if (Value is Int32 || Value is Int64)
      {
        Cell.RemoveAttributeNode(Cell.GetAttributeNode("t"));
        Cell.RemoveAttributeNode(Cell.GetAttributeNode("s"));
        Cell.FirstChild.InnerText = Value.ToString();
        return true;
      }
      if (Value is Int32 || Value is Int64 || Value is Double || Value is Single)
      {
        Cell.RemoveAttributeNode(Cell.GetAttributeNode("t"));        
        Cell.FirstChild.InnerText = Value.ToString();
        return true;
      }

      return false;
    }
    Object getCellValue(XmlElement Cell)
    {
      XmlAttribute Attr = Cell.GetAttributeNode("t");
      if (Attr!=null && Attr.Value=="s")//String      
        return GetString(Cell.FirstChild.InnerText);
      Int32 ValueInt32=0;
      if (Cell.FirstChild!=null&&Int32.TryParse(Cell.FirstChild.InnerText,out ValueInt32)) return ValueInt32;
      Int64 ValueInt64=0;
      if (Cell.FirstChild!=null&&Int64.TryParse(Cell.FirstChild.InnerText,out ValueInt64)) return ValueInt64;
      return null;
    }
    bool ReplaceCell(XmlElement Cell,Dictionary<String,Object> Poles)
    {
        XmlAttribute Attr = Cell.GetAttributeNode("t");
        if (Attr!=null && Attr.Value=="s")
        {
          Object Value = getCellValue(Cell);
          if (Value is String)
          {
            String ValueS = (String)Value;
            foreach(var Pole in Poles)
            {              
              if (ValueS==Pole.Key)// Ключ занимает всю ячейку
              {
                setCellValue(Cell,Pole.Value);
              }
              else// Ключ занимает только часть ячейки
              {
                String r = ValueS.Replace(Pole.Key,Pole.Value.ToString());
                if (ValueS!=r) setCellValue(Cell,r);
              }
            }
          }
        }
        return true;
    }
    bool OffsetRowsMergeCell(XmlElement Row,Int32 Offset)
    {
      Int32 r=0;
      if (Row==null) 
        return false;
      var FirstCol = new List<String>();
      var LastCol = new List<String>();
      var Col = new List<XmlElement>();
      if (Int32.TryParse(Row.GetAttribute("r"),out r))
      {
        XmlElement Worksheet = Row.ParentNode==null?null:Row.ParentNode.ParentNode as XmlElement;
        if (Worksheet!=null)
        {
          var MergeCellsList =  Worksheet.GetElementsByTagName("mergeCells");
          if (MergeCellsList.Count>0)
          {
            var MergeCells = MergeCellsList[0] as XmlElement;
            if (MergeCells!=null)
              foreach(XmlElement mergeCell in MergeCells.GetElementsByTagName("mergeCell"))
            {
              String cr = mergeCell.GetAttribute("ref");
              String S1="";
              String N1="";
              String S2="";
              String N2="";
              Int32 Pos = 0;
              for(;Pos<cr.Length && cr[Pos]>='A' && cr[Pos]<='Z';Pos++) 
                  S1+=cr[Pos];              
              for(;Pos<cr.Length && cr[Pos]>='0' && cr[Pos]<='9';Pos++) 
                  N1+=cr[Pos];
              Pos++;
              for(;Pos<cr.Length && cr[Pos]>='A' && cr[Pos]<='Z';Pos++)
                  S2+=cr[Pos];              
              for(;Pos<cr.Length && cr[Pos]>='0' && cr[Pos]<='9';Pos++) 
                  N2+=cr[Pos];   
              Int32 n1=0;
              Int32 n2=0;
              Int32.TryParse(N1,out n1);
              Int32.TryParse(N2,out n2);
              if (n1>r) 
                n1+=Offset-1;
              if (n2>r) 
                n2+=Offset-1;
              if (n1==r && n2==r)
              {
                FirstCol.Add(S1);
                LastCol.Add(S2);
                Col.Add(mergeCell);
              }
              N1 = n1.ToString();
              N2 = n2.ToString();
              mergeCell.SetAttribute("ref",S1+N1+":"+S2+N2);
            }                        
            for(Int32 j=0;j<Offset;j++)// 1 - 
            {              
              for(Int32 i=0;i<FirstCol.Count;i++)
              {
                var m = (XmlElement)Col[i].Clone();
                Int32 n1=r+j;
                Int32 n2=r+j;
                String N1 = n1.ToString();
                String N2 = n2.ToString();
                m.SetAttribute("ref",FirstCol[i]+N1+":"+LastCol[i]+N2);
                m.SetAttribute("raf",FirstCol[i]+N1+":"+LastCol[i]+N2);
                MergeCells.AppendChild(m);
              }
              
            } 
            for(Int32 i=0;i<FirstCol.Count;i++)
              MergeCells.RemoveChild(Col[i]);            
            MergeCells.SetAttribute("count",MergeCells.GetElementsByTagName("mergeCell").Count.ToString());
          }
        }      
      }
      return true;
    }
    bool OffsetRows(XmlElement Row,Int32 Offset)
    {
      
      Int32 r=0;
      if (Int32.TryParse(Row.GetAttribute("r"),out r))
      {
        Row.SetAttribute("r",(r+Offset).ToString());
        foreach(XmlElement c in Row.GetElementsByTagName("c"))
        {
          String cr = c.GetAttribute("r");
          for(int i=0;i<cr.Length;i++) 
            if (cr[i]<'A' || cr[i]>'Z') 
          {
            c.SetAttribute("r",cr.Remove(i)+(r+Offset).ToString()); 
            break;
          }
        }
      }     
        
      
      //
      return true;
    }
    bool ReplaceWorksheet(XmlElement Worksheet)
    {
      //Замена полей
      foreach(XmlElement c in Worksheet.GetElementsByTagName("c")) ReplaceCell(c,Poles);        
      //Вставка строк
      foreach(var Table in Tables)
      {
        XmlElement Cell = null;
        do
        {
          Cell = null;
          //Найти таблицу
          foreach(XmlElement c in Worksheet.GetElementsByTagName("c")) 
          {
            Object Value = getCellValue(c);
            if (Value is String && ((String)Value).IndexOf(Table.Key)!=-1){Cell = c; break;}            
          }
          if (Cell!=null)
          {
            XmlElement Row = (XmlElement)Cell.ParentNode;
            Int32 Offset=0;
            foreach(var r in Table.Value)
            {
              var NewRow = (XmlElement)Row.Clone();
              foreach(XmlElement c in NewRow.GetElementsByTagName("c")) ReplaceCell(c,r);
              OffsetRows(NewRow,Offset);
              Row.ParentNode.InsertBefore(NewRow,Row);
              Offset+=1;                
            }
            XmlElement NextRow = (XmlElement)Row.NextSibling;
            for(;NextRow!=null;NextRow = (XmlElement)NextRow.NextSibling) 
              OffsetRows(NextRow,Offset-1);
            OffsetRowsMergeCell(Row,Offset);
            Row.ParentNode.RemoveChild(Row);
            
          }
        }while(Cell!=null);
        
      }
      return true;
    }
    public override bool EditDocument(TaskManager.Task task = null)
    {      
      // Считать документ
      XmlDocument Workbook = new XmlDocument();      
      Workbook.LoadXml(Encoding.UTF8.GetString(m_zip.readAll("xl/workbook.xml")));
      LoadStrings();
      var Rels = new XmlDocument();      
      Rels.LoadXml(Encoding.UTF8.GetString(m_zip.readAll("xl/_rels/workbook.xml.rels")));
      Dictionary<String,String> RelFiles = new Dictionary<String, String>();
      foreach (XmlElement Relationship in Rels.GetElementsByTagName("Relationship"))
        RelFiles[Relationship.GetAttribute("Id")] = Relationship.GetAttribute("Target");
      var list = Workbook.GetElementsByTagName("sheet");
      if (task!=null)
      {
      	task.TaskName = "Заполнение шаблона";
      	task.TaskMax = list.Count;
      	task.TaskValue = 0;
      }              
      foreach (XmlElement sheet in list)
      {        
        XmlDocument SheetXML = new XmlDocument();
        SheetXML.LoadXml(Encoding.UTF8.GetString(m_zip.readAll("xl/"+RelFiles[sheet.GetAttribute("r:id")])));
        ReplaceWorksheet(SheetXML.DocumentElement);
        m_zip.writeAll("xl/"+RelFiles[sheet.GetAttribute("r:id")],Encoding.UTF8.GetBytes(SheetXML.InnerXml));
        if (task!=null) task.TaskValue += 1;
      }
      SaveStrings();
      //Внести изменения
      /*foreach(var item in Poles) replace(Workbook.DocumentElement,item.Key, item.Value);
      foreach(var item in Tables) InsertRows(Workbook.DocumentElement, item.Key,item.Value);      */
      //Сохранить измения
      //m_zip.writeAll("xl/workbook.xml",Encoding.UTF8.GetBytes(Workbook.InnerXml));
      return true;
    }
  }
}
